﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class EFHelper<TDatabase> where TDatabase : DbContext, new()
    {
        /// <summary>
        /// 定义一个私有的数据库上下文对象
        /// </summary>
        private static TDatabase _dbContext;

        /// <summary>
        /// 数据库上下文方法
        /// </summary>
        /// <returns></returns>
        private TDatabase GetDbContext()
        {
            if (_dbContext == null)
            {
                _dbContext = new TDatabase();

            }
            return _dbContext;
        }

        #region 查询


        /// <summary>
        /// 有条件和无条件查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public List<T> QueryList<T>(Expression<Func<T, bool>> predicate) where T : class
        {
            try
            {
                var dbContext = GetDbContext();
                if (predicate == null)
                    return dbContext.Set<T>().AsNoTracking().ToList();
                else
                    return dbContext.Set<T>().Where(predicate).ToList();
            }
            catch (Exception e)
            {

                return null;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageNum">当前页码数</param>
        /// <param name="pageSize">当前显示数据数</param>
        /// <returns></returns>
        public List<T> PageQuery<T>(Expression<Func<T, bool>> predicate, Expression<Func<T, DateTime>> order, int pageNum, int pageSize) where T : class
        {
            try
            {
                var dbContext = GetDbContext();
                if (predicate == null)
                    return dbContext.Set<T>().OrderBy(order).ToList().Skip((pageNum - 1) * pageSize).Take(pageSize).ToList();
                else
                    return dbContext.Set<T>().Where(predicate).OrderBy(order).ToList().Skip((pageNum - 1) * pageSize).Take(pageSize).ToList();
            }
            catch (Exception)
            {

                return new List<T>();
            }
        }

        /// <summary>
        /// 有条件查询返回单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public T GetInfo<T>(Expression<Func<T, bool>> predicate) where T : class
        {
            try
            {
                var dbContext = GetDbContext();
                return dbContext.Set<T>().Where(predicate).ToList().FirstOrDefault();
            }
            catch (Exception)
            {

                return null;
            }
        }
        /// <summary>
        /// SqlQuery
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlstr"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public List<T> SqlQuery<T>(string sqlstr, SqlParameter[] pars) where T : class
        {
            try
            {
                var dbContext = GetDbContext();
                return dbContext.Database.SqlQuery<T>(sqlstr, pars).ToList();
            }
            catch (Exception ex)
            {

                return new List<T>();
            }
        }

        public List<T> SqlQuery<T>(string sqlstr) where T : class
        {
            try
            {
                var dbContext = GetDbContext();
                return dbContext.Database.SqlQuery<T>(sqlstr).ToList();
            }
            catch (Exception ex)
            {

                return new List<T>();
            }
        }

        /// <summary>
        /// EF SQL 语句返回 dataTable
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable SqlQueryForDataTatable<T>(
                 string sql) where T : class
        {

            try
            {
                var db = GetDbContext().Database;
                SqlConnection conn = new System.Data.SqlClient.SqlConnection();
                conn.ConnectionString = "server=.;database=OASystem;uid=sa;pwd=123456";
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                conn = (SqlConnection)db.Connection;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                adapter.Fill(table);

                conn.Close();//连接需要关闭
                conn.Dispose();
                return table;
            }
            catch (Exception ex)
            {

                throw;
            }
        }
        #endregion

        #region 增、删、改
        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T">泛型简化名称</typeparam>
        /// <param name="t">参数</param>
        /// <returns></returns>
        public int Insert<T>(T t) where T : class
        {
            try
            {
                //调用数据库上下文的方法
                var dbContext = GetDbContext();
                var entry = dbContext.Entry<T>(t);
                //将新对象添加到数据库上下文
                entry.State = EntityState.Added;
                //通知数据库更新数据库
                return dbContext.SaveChanges();

            }
            catch (Exception ex)
            {

                return 0;
            }
        }

       
        //public int Modify<T>(T t) where T : class
        //{
        //    try
        //    {
        //        var dbContext = GetDbContext();
        //        var entry = dbContext.Entry<T>(t);
        //        entry.State = EntityState.Modified;
        //        return dbContext.SaveChanges();
        //    }
        //    catch (Exception ex)
        //    {

        //        return 0;
        //    }
        //}

        /// <summary>
        /// 修改方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Modify<T>(T entity) where T : class
        {
            var dbContext = GetDbContext();

            if (entity == null)
            {
                throw new ArgumentException("entity");
            }
            if (dbContext.Entry(entity).State == EntityState.Detached)
            {
                HandleDetached(entity);
            }
            dbContext.Set<T>().Attach(entity);
            dbContext.Entry(entity).State = EntityState.Modified;
            return dbContext.SaveChanges();
        }

        private bool HandleDetached<T>(T entity) where T : class
        {
            var dbContext = GetDbContext(); 
            var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;
            var entitySet = objectContext.CreateObjectSet<T>();
            var entityKey = objectContext.CreateEntityKey(entitySet.EntitySet.Name, entity);
            object foundSet;
            bool exists = objectContext.TryGetObjectByKey(entityKey, out foundSet);
            if (exists)
            {
                objectContext.Detach(foundSet); //从上下文中移除
            }
            return exists;
        }

        /// <summary>
        /// 删除方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Delete<T>(T t) where T : class
        {
            try
            {
                var dbContext = GetDbContext();
                var entry = dbContext.Entry<T>(t);
                entry.State = EntityState.Deleted;
                return dbContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool Delete<T>(List<T> list) where T : class
        {
            var num = 0;
            try
            {
                if (list.Count != 0)
                {
                    foreach (var item in list)
                    {
                        num += Delete<T>(item);
                    }
                }
                if (num == list.Count)
                    return true;
                else
                    return false;
            }
            catch (Exception)
            {

                throw;
            }

        }



        #endregion
    }
}
